package dao;


import domin.Resource;
import domin.Role;
import service.RoleService;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public final class ResourceDao {
    private static ResourceDao resourceDao = null;
    private ResourceDao() {
    }
    public static ResourceDao getInstance() {
        if (resourceDao == null) {
            resourceDao = new ResourceDao();
        }
        return resourceDao;
    }
    public Resource find(Connection connection, Integer id) throws SQLException {
        Resource desiredResource = null;
        String sql_select_id = "SELECT * FROM resource WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()) {
            String description = resultSet_select_id.getString("description");
            String resource = resultSet_select_id.getString("resource");
            String url = resultSet_select_id.getString("url");
            String remarks = resultSet_select_id.getString("remarks");
            desiredResource = new Resource(id, description, url, resource, remarks);
        }
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredResource;
    }

    public Collection<Resource> findAll(Connection connection, Integer roleId)
            throws SQLException {
        Collection<Resource> desiredResources = new TreeSet<Resource>();
        String sql_select_role_id = "SELECT * FROM resource WHERE role_id = ?";
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select_role_id);
        pstmt_select.setInt(1, roleId);
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String resource = resultSet_select.getString("resource");
            String no = resultSet_select.getString("no");
            String url = resultSet_select.getString("url");
            String remarks = resultSet_select.getString("remarks");
            Resource desiredResource = new Resource(id, resource, url, no, remarks);
            desiredResources.add(desiredResource);
        }
        return desiredResources;
    }
    public Collection<Resource> findAll(Connection connection, Pagination pagination, List<Condition> conditionList)
            throws SQLException {
        Collection<Resource> desiredResources = new TreeSet<Resource>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM Resource ");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList.toString());
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection, "Resource");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }

        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select_all.executeQuery();
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String description = resultSet_select.getString("resource");
            String no = resultSet_select.getString("no");
            String url = resultSet_select.getString("url");
            String remarks = resultSet_select.getString("remarks");
            int roleId = resultSet_select.getInt("role_id");
            Resource desiredResource = new Resource(id, description, url, no, remarks);
            desiredResources.add(desiredResource);
        }
        JdbcHelper.close(resultSet_select, pstmt_select_all);
        return desiredResources;
    }


}
